package com.fu.jdbc01.dao;


import com.fu.jdbc01.domain.Student;
import com.fu.jdbc01.utils.JDBCUtils;


import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;


public class StudentDaoImpl implements StudentDao {

    @Override
    public ArrayList<Student> findAll() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        ArrayList<Student> list = new ArrayList<>();

        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * FROM student";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                Student student = new Student(sid, name, age, birthday);
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           JDBCUtils.close(connection,statement,resultSet);
        }
        return list;
    }


    @Override
    public Student findById(Integer id) {
        Student student = new Student();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from student where sid='"+id+"'";
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                Integer sid = resultSet.getInt("sid");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                student.setSid(sid);
                student.setName(name);
                student.setAge(age);
                student.setBirthday(birthday);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(connection,statement,resultSet);
        }
        return student;
    }


    @Override
    public int insert(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            java.util.Date birthday = stu.getBirthday();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String format = simpleDateFormat.format(birthday);
            String sql = "insert into student values ('" + stu.getSid() + "','" + stu.getName() + "','" + stu.getAge() + "','" + format + "')";
            result = statement.executeUpdate(sql);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(connection,statement);
        }
        return result;
    }


    @Override
    public int update(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            java.util.Date birthday = stu.getBirthday();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String format = simpleDateFormat.format(birthday);
            String sql = "update  student set sid='" + stu.getSid() + "',name='" + stu.getName() + "',age='" + stu.getAge() + "',birthday='" + format + "' where sid='" + stu.getSid() + "'";
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(connection, statement);
        }
        return result;
    }


    @Override
    public int delete(Integer id) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db14", "root", "123456");
            statement = connection.createStatement();

            String sql = "delete from student where sid='" + id + "'";
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(connection,statement);
        }

        return result;
    }
}
